{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SYS_NAME</th>\n",
       "      <th>NAME</th>\n",
       "      <th>TARGET_ID</th>\n",
       "      <th>DESCRIPTION</th>\n",
       "      <th>ENTITY</th>\n",
       "      <th>VALUE</th>\n",
       "      <th>COLLECTTIME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>184</td>\n",
       "      <td>磁盘已使用大小</td>\n",
       "      <td>C:\\</td>\n",
       "      <td>3.427079e+07</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>184</td>\n",
       "      <td>磁盘已使用大小</td>\n",
       "      <td>D:\\</td>\n",
       "      <td>8.026259e+07</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>183</td>\n",
       "      <td>磁盘容量</td>\n",
       "      <td>C:\\</td>\n",
       "      <td>5.232332e+07</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>183</td>\n",
       "      <td>磁盘容量</td>\n",
       "      <td>D:\\</td>\n",
       "      <td>1.572833e+08</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>184</td>\n",
       "      <td>磁盘已使用大小</td>\n",
       "      <td>C:\\</td>\n",
       "      <td>3.432890e+07</td>\n",
       "      <td>2014-10-02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  SYS_NAME     NAME  TARGET_ID DESCRIPTION ENTITY         VALUE COLLECTTIME\n",
       "0   财务管理系统  CWXT_DB        184     磁盘已使用大小    C:\\  3.427079e+07  2014-10-01\n",
       "1   财务管理系统  CWXT_DB        184     磁盘已使用大小    D:\\  8.026259e+07  2014-10-01\n",
       "2   财务管理系统  CWXT_DB        183        磁盘容量    C:\\  5.232332e+07  2014-10-01\n",
       "3   财务管理系统  CWXT_DB        183        磁盘容量    D:\\  1.572833e+08  2014-10-01\n",
       "4   财务管理系统  CWXT_DB        184     磁盘已使用大小    C:\\  3.432890e+07  2014-10-02"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# -*- coding:utf-8 -*-\n",
    "# 数据预处理\n",
    "#---* 1 *---数据清洗\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from pandas import DataFrame,Series\n",
    "\n",
    "inputfile1 = 'discdata.xls'\n",
    "data = pd.read_excel(inputfile1)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SYS_NAME</th>\n",
       "      <th>NAME</th>\n",
       "      <th>TARGET_ID</th>\n",
       "      <th>DESCRIPTION</th>\n",
       "      <th>ENTITY</th>\n",
       "      <th>VALUE</th>\n",
       "      <th>COLLECTTIME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>184</td>\n",
       "      <td>磁盘已使用大小</td>\n",
       "      <td>C:\\</td>\n",
       "      <td>3.427079e+07</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>184</td>\n",
       "      <td>磁盘已使用大小</td>\n",
       "      <td>D:\\</td>\n",
       "      <td>8.026259e+07</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>183</td>\n",
       "      <td>磁盘容量</td>\n",
       "      <td>C:\\</td>\n",
       "      <td>5.232332e+07</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>183</td>\n",
       "      <td>磁盘容量</td>\n",
       "      <td>D:\\</td>\n",
       "      <td>1.572833e+08</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>184</td>\n",
       "      <td>磁盘已使用大小</td>\n",
       "      <td>C:\\</td>\n",
       "      <td>3.432890e+07</td>\n",
       "      <td>2014-10-02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  SYS_NAME     NAME  TARGET_ID DESCRIPTION ENTITY         VALUE COLLECTTIME\n",
       "0   财务管理系统  CWXT_DB        184     磁盘已使用大小    C:\\  3.427079e+07  2014-10-01\n",
       "1   财务管理系统  CWXT_DB        184     磁盘已使用大小    D:\\  8.026259e+07  2014-10-01\n",
       "2   财务管理系统  CWXT_DB        183        磁盘容量    C:\\  5.232332e+07  2014-10-01\n",
       "3   财务管理系统  CWXT_DB        183        磁盘容量    D:\\  1.572833e+08  2014-10-01\n",
       "4   财务管理系统  CWXT_DB        184     磁盘已使用大小    C:\\  3.432890e+07  2014-10-02"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 一般情况下默认磁盘容量是定值，所以需要剔除磁盘容量重复的数据\n",
    "data.drop_duplicates(data.columns[:-1],inplace=True)\n",
    "data.to_excel('dataCleaned.xlsx')\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#---* 2 *---属性构造 \n",
    "# 思路：由于每台服务器上的这三个属性值一直不变：NAME、TARGET_ID、ENTITY，将这三个属性值合并\n",
    "inputfile2 = 'dataCleaned.xlsx'\n",
    "data = pd.read_excel(inputfile2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.generic.DataFrameGroupBy object at 0x0000000008F81710>"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 教材上方法一：\n",
    "df = data[data['TARGET_ID'] == 184].copy() # 只选取TARGET_ID为184的数据\n",
    "df_group = df.groupby('COLLECTTIME') # 以时间分组得到一个GroupBy对象\n",
    "df_group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SYS_NAME</th>\n",
       "      <th>CWXT_DB:184:C:\\</th>\n",
       "      <th>CWXT_DB:184:D:\\</th>\n",
       "      <th>COLLECTTIME</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COLLECTTIME</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2014-10-01</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34270787.33</td>\n",
       "      <td>80262592.65</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-02</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34328899.02</td>\n",
       "      <td>83200151.65</td>\n",
       "      <td>2014-10-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-03</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34327553.50</td>\n",
       "      <td>83208320.00</td>\n",
       "      <td>2014-10-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-04</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34288672.21</td>\n",
       "      <td>83099271.65</td>\n",
       "      <td>2014-10-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-05</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34190978.41</td>\n",
       "      <td>82765171.65</td>\n",
       "      <td>2014-10-05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            SYS_NAME  CWXT_DB:184:C:\\  CWXT_DB:184:D:\\ COLLECTTIME\n",
       "COLLECTTIME                                                       \n",
       "2014-10-01    财务管理系统      34270787.33      80262592.65  2014-10-01\n",
       "2014-10-02    财务管理系统      34328899.02      83200151.65  2014-10-02\n",
       "2014-10-03    财务管理系统      34327553.50      83208320.00  2014-10-03\n",
       "2014-10-04    财务管理系统      34288672.21      83099271.65  2014-10-04\n",
       "2014-10-05    财务管理系统      34190978.41      82765171.65  2014-10-05"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "#定义属性变换函数\n",
    "def attr_trans(x):\n",
    "    result = Series(index = ['SYS_NAME','CWXT_DB:184:C:\\\\','CWXT_DB:184:D:\\\\','COLLECTTIME'])\n",
    "    result['SYS_NAME'] = x['SYS_NAME'].iloc[0]\n",
    "    result['COLLECTTIME'] = x['COLLECTTIME'].iloc[0]\n",
    "    result['CWXT_DB:184:C:\\\\'] = x['VALUE'].iloc[0]\n",
    "    result['CWXT_DB:184:D:\\\\'] = x['VALUE'].iloc[1]\n",
    "    return result \n",
    "data_attr_constr = df_group.apply(attr_trans)# 逐组处理\n",
    "data_attr_constr.to_excel('attrsConstruction.xlsx',index=False)\n",
    "data_attr_constr.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "COLLECTTIME\n",
      "2014-10-01    2\n",
      "2014-10-02    2\n",
      "2014-10-03    2\n",
      "2014-10-04    2\n",
      "2014-10-05    2\n",
      "2014-10-06    2\n",
      "2014-10-07    2\n",
      "2014-10-08    2\n",
      "2014-10-09    2\n",
      "2014-10-10    2\n",
      "2014-10-11    2\n",
      "2014-10-12    2\n",
      "2014-10-13    2\n",
      "2014-10-14    2\n",
      "2014-10-15    2\n",
      "2014-10-16    2\n",
      "2014-10-17    2\n",
      "2014-10-18    2\n",
      "2014-10-19    2\n",
      "2014-10-20    2\n",
      "2014-10-21    2\n",
      "2014-10-22    2\n",
      "2014-10-23    2\n",
      "2014-10-24    2\n",
      "2014-10-25    2\n",
      "2014-10-26    2\n",
      "2014-10-27    2\n",
      "2014-10-28    2\n",
      "2014-10-29    2\n",
      "2014-10-30    2\n",
      "2014-10-31    2\n",
      "2014-11-01    2\n",
      "2014-11-02    2\n",
      "2014-11-03    2\n",
      "2014-11-04    2\n",
      "2014-11-05    2\n",
      "2014-11-06    2\n",
      "2014-11-07    2\n",
      "2014-11-08    2\n",
      "2014-11-09    2\n",
      "2014-11-10    2\n",
      "2014-11-11    2\n",
      "2014-11-12    2\n",
      "2014-11-13    2\n",
      "2014-11-14    2\n",
      "2014-11-15    2\n",
      "2014-11-16    2\n",
      "dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SYS_NAME</th>\n",
       "      <th>CWXT_DB:184:C:\\</th>\n",
       "      <th>CWXT_DB:184:D:\\</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COLLECTTIME</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2014-10-01</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34270787.33</td>\n",
       "      <td>80262592.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-02</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34328899.02</td>\n",
       "      <td>83200151.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-03</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34327553.50</td>\n",
       "      <td>83208320.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-04</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34288672.21</td>\n",
       "      <td>83099271.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-05</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34190978.41</td>\n",
       "      <td>82765171.65</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            SYS_NAME  CWXT_DB:184:C:\\  CWXT_DB:184:D:\\\n",
       "COLLECTTIME                                           \n",
       "2014-10-01    财务管理系统      34270787.33      80262592.65\n",
       "2014-10-02    财务管理系统      34328899.02      83200151.65\n",
       "2014-10-03    财务管理系统      34327553.50      83208320.00\n",
       "2014-10-04    财务管理系统      34288672.21      83099271.65\n",
       "2014-10-05    财务管理系统      34190978.41      82765171.65"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#方法二，死方法，没有方法一灵活\n",
    "df_g = df.groupby('COLLECTTIME').size()#size跟count的区别： size计数时包含NaN值，而count不包含NaN值\n",
    "#print(df_g)\n",
    "indexpre = df_g.index\n",
    "#print(indexpre)\n",
    "data_processed = DataFrame([],index = indexpre, columns=['SYS_NAME','CWXT_DB:184:C:\\\\','CWXT_DB:184:D:\\\\'])\n",
    "data_processed['SYS_NAME'] =  u'财务管理系统'\n",
    "data_processed['CWXT_DB:184:C:\\\\'] = df['VALUE'][df['ENTITY']=='C:\\\\'].values\n",
    "data_processed['CWXT_DB:184:D:\\\\'] = df['VALUE'][df['ENTITY']=='D:\\\\'].values\n",
    "data_processed.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
